﻿using FreeSqlDemo.Modules;
using FreeSqlDemo.Utility;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace FreeSqlDemo.Services
{
    public class NpoiExcelExportService
    {
        private readonly IWebHostEnvironment _webHostEnvironment;

        public NpoiExcelExportService(IWebHostEnvironment webHostEnvironment)//IHostingEnvironment iEnvironment,
        {
            _webHostEnvironment = webHostEnvironment;
        }

        /// <summary>
        /// Excel数据导出
        /// </summary>
        /// <param name="resultMsg">导出结果</param>
        /// <param name="excelFilePath">保存excel文件路径</param>
        /// <returns></returns>
        public bool ExcelDataExport(out string resultMsg, out string excelFilePath)
        {
            var result = true;
            excelFilePath = "";
            resultMsg = "successfully";
            //Excel导出名称
            string excelName = "学生表";
            try
            {
                //首先创建Excel文件对象
                var workbook = new HSSFWorkbook();

                //创建工作表，也就是Excel中的sheet，给工作表赋一个名称(Excel底部名称)
                var sheet = workbook.CreateSheet(excelName);

                //sheet.DefaultColumnWidth = 20;//默认列宽

                sheet.ForceFormulaRecalculation = true;//TODO:是否开始Excel导出后公式仍然有效(非必须)

                #region 标题样式
                //设置顶部大标题样式
                var cellStyleFont = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 20, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Coral.Index, HSSFColor.White.Index,
                 FontUnderlineType.None, FontSuperScript.None, false);

                #region 合并单元格例第1行到第2行第3列到第4列围成的矩形区域
                /**
                  第一个参数：从第几行开始合并
                  第二个参数：到第几行结束合并
                  第三个参数：从第几列开始合并
                  第四个参数：到第几列结束合并
                
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
                sheet.AddMergedRegion(region);
                **/
                #endregion

                //第一行表单
                var row = NpoiExcelExportHelper._.CreateRow(sheet, 0, 28);
                var cell = row.CreateCell(0);

                cell.SetCellValue(excelName);//合并单元格后，只需对第一个位置赋值即可（TODO:顶部标题）
                cell.CellStyle = cellStyleFont;

                //二级标题列样式设置
                var headTopStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 15, true, 700, "楷体", true, false, false, true, FillPattern.SolidForeground, HSSFColor.Grey25Percent.Index, HSSFColor.Black.Index,
                FontUnderlineType.None, FontSuperScript.None, false);

                //表头名称
                var headerName = new[] { "编号", "姓名", "性别", "手机号", "描述", "爱好" };
                row = NpoiExcelExportHelper._.CreateRow(sheet, 1, 24);//第二行

                for (var i = 0; i < headerName.Length; i++)
                {
                    cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, i, headerName[i]);

                    //设置单元格宽度
                    if (headerName[i] == "描述" || headerName[i] == "爱好")
                    {
                        sheet.SetColumnWidth(i, 8000);
                    }
                    else
                    {
                        sheet.SetColumnWidth(i, 4000);
                    }
                }
                #endregion


                #region 单元格内容信息
                var cellStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 10, true, 400);

                #region 左侧列单元格合并
                //TODO:关于Excel行列单元格合并问题（合并单元格后，只需对第一个位置赋值即可）
                /**
                  第一个参数：从第几行开始合并
                  第二个参数：到第几行结束合并
                  第三个参数：从第几列开始合并
                  第四个参数：到第几列结束合并
                **/
                CellRangeAddress leftOne = new CellRangeAddress(2, 7, 0, 0);

                sheet.AddMergedRegion(leftOne);

                CellRangeAddress leftTwo = new CellRangeAddress(8, 11, 0, 0);

                sheet.AddMergedRegion(leftTwo);
                #endregion

                var connectionString = "Data Source = 39.105.20.156; Port = 3306; User ID = root; Password = root; Initial Catalog = demo;Charset=utf8;";
                var Fsql = new FreeSql.FreeSqlBuilder()
                           .UseConnectionString(FreeSql.DataType.MySql, connectionString)
                            .UseAutoSyncStructure(true)
                            .UseLazyLoading(true)
                            .UseNoneCommandParameter(true)
                            .Build();
                var userInfo = Fsql.Select<UserInfo>().ToList();

                var Id = userInfo.Select(m => m.Id).ToList();
                var UserName = userInfo.Select(m => m.UserName).ToList();
                var Sex = userInfo.Select(m => m.Sex).ToList();
                var Phone = userInfo.Select(m => m.Phone).ToList();
                var Description = userInfo.Select(m => m.Description).ToList();
                var Hobby = userInfo.Select(m => m.Hobby).ToList();

                for (int i = 0; i < userInfo.Count(); i++)
                {
                    row = NpoiExcelExportHelper._.CreateRow(sheet, i + 2, 20); //sheet.CreateRow(i+2);//在上面表头的基础上创建行
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 0, Id[i].ToString());//编号
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 1, UserName[i].ToString());//姓名
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 2, Sex[i].ToString());//性别
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 3, Phone[i].ToString());//手机号
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 4, Description[i].ToString());//描述
                    cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, 5, Hobby[i].ToString());//爱好
                }
                #endregion

                string folder = DateTime.Now.ToString("yyyyMMdd");//当前时间转换

                var aa = _webHostEnvironment.ContentRootPath;

                //保存文件到静态资源文件夹中（wwwroot）,使用绝对路径
                var uploadPath = _webHostEnvironment.ContentRootPath + "/UploadFile/" + folder + "/";
               //var uploadPath = "E:\\Demo\\excel-import-my-sql\\FreeSqlDemo" + "/UploadFile/" + folder + "/";

                //excel保存文件名
                string excelFileName = excelName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

                //创建目录文件夹
                if (!Directory.Exists(uploadPath))
                {
                    Directory.CreateDirectory(uploadPath);
                }

                //Excel的路径及名称
                string excelPath = uploadPath + excelFileName;

                //使用FileStream文件流来写入数据（传入参数为：文件所在路径，对文件的操作方式，对文件内数据的操作）
                var fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);

                //向Excel文件对象写入文件流，生成Excel文件
                workbook.Write(fileStream);

                //关闭文件流
                fileStream.Close();

                //释放流所占用的资源
                fileStream.Dispose();

                //excel文件保存的相对路径，提供前端下载
                var relativePositioning = "/UploadFile/" + folder + "/" + excelFileName;

                excelFilePath = relativePositioning;
            }
            catch (Exception e)
            {
                result = false;
                resultMsg=e.Message;
            }
            return result;
        }
    }
}
